﻿<!DOCTYPE html>
<html>

<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>【MySQL索引（上）】（重点）</title>
  <link rel="stylesheet" href="https://stackedit.io/style.css" />
</head>

<body class="stackedit">
  <div class="stackedit__html"><p></p><div class="toc"><h3>文章目录</h3><ul><li><a href="#_13">一、见见索引</a></li><li><a href="#_36">二、认识磁盘</a></li><li><a href="#_52">三、索引的学习</a></li><ul><li><a href="#1_54">1.建立共识</a></li><li><a href="#2page_64">2.重谈page</a></li><li><a href="#3pagepage_126">3.单page和多page同样存在效率低下的问题</a></li><ul><li><a href="#page_128">单page的缺陷</a></li><li><a href="#page_149">多page的缺陷</a></li></ul><li><a href="#_160">页目录</a></li><ul><li><a href="#page_163">单page</a></li><li><a href="#page_176">多page</a></li></ul></ul><li><a href="#B_198">B+树为什么行！</a></li><ul><ul><li><a href="#_199">详谈细节</a></li></ul></ul><li><a href="#_218">其他数据结构为什么不行?</a></li><ul><ul><li><a href="#_238">聚簇索引和非聚簇索引</a></li></ul><li><a href="#_266">回表查询</a></li></ul><li><a href="#_285">四、索引的操作</a></li><ul><li><a href="#1_287">1.创建主键索引</a></li><li><a href="#2_314">2.唯一索引的创建</a></li><li><a href="#3_339">3.普通索引的创建</a></li></ul></ul></div><p></p>
<hr>
<hr>
<h1><a id="_13"></a>一、见见索引</h1>
<p>导入800w条数据。</p>
<p>表的结构如下：<br>
<img src="https://img-blog.csdnimg.cn/direct/d8da1eef2ae34c74bd56eec9d178f6bb.png" alt="在这里插入图片描述"><br>
查询员工编号为998877的员工：<br>
<img src="https://img-blog.csdnimg.cn/direct/2ba4ea2b5506456a8621b46e13e97ee4.png" alt="在这里插入图片描述"><br>
可以看到，查询该数据，花费了4.64s，这对我们客户来说是无法容忍的。</p>
<p>解决方案：</p>
<ul>
<li>为empno创建索引</li>
</ul>
<blockquote>
<p>alter table EMP add index(empno)</p>
</blockquote>
<p>再次查询：<br>
<img src="https://img-blog.csdnimg.cn/direct/c4f7bc21a597437fb50b42c8330fc9d1.png" alt="在这里插入图片描述"></p>
<p>查询时间变成了0.01s，这快了n倍！！！</p>
<p><strong>这个就是索引。</strong></p>
<h1><a id="_36"></a>二、认识磁盘</h1>
<p>硬件方面学不明白，直接看课件即可。</p>
<p>讲一个案例：<br>
假设MySQL要向磁盘获取16kb空间大小的内容。</p>
<p>具体过程如下：<br>
<img src="https://img-blog.csdnimg.cn/direct/82257d7874c54a499558e9cb14a06056.png" alt="在这里插入图片描述"></p>
<p>MySQL想要从磁盘中获取16KB大小的文件内容，首先向操作系统申请16KB大小的空间，操作系统不是直接从自己的文件缓冲区拿的，而是将16KB大小转化成4*4KB大小，去磁盘系统中，按单位获取文件大小，再返回给MySQL。<br>
这是实际过程。</p>
<p>但从逻辑上看，就是MySQL向磁盘系统获取16KB大小内容。</p>
<h1><a id="_52"></a>三、索引的学习</h1>
<h2><a id="1_54"></a>1.建立共识</h2>
<ul>
<li>MySQL 中的数据文件，是以page为单位保存在磁盘当中的。<br>
MySQL 的 CURD 操作，都需要通过计算，找到对应的插入位置，或者找到对应要修改或者查询的数据。</li>
<li>而只要涉及计算，就需要CPU参与，而为了便于CPU参与，一定要能够先将数据移动到内存当中。所以在特定时间内，数据一定是磁盘中有，内存中也有。后续操作完内存数据之后，以特定的刷新策略，刷新到磁盘。而这时，就涉及到磁盘和内存的数据交互，也就是IO了。而此时IO的基本单位就是Page。</li>
<li>为了更好的进行上面的操作， MySQL 服务器在内存中运行的时候，在服务器内部，就申请了被称为 Buffer Pool 的的大内存空间，来进行各种缓存。其实就是很大的内存空间，来和磁盘数据进行IO交互。</li>
<li>为了更高的效率，一定要尽可能的减少系统和磁盘IO的次数</li>
</ul>
<h2><a id="2page_64"></a>2.重谈page</h2>
<p>先建立一个测试表：</p>
<blockquote>
<p>create table if not exists user (<br>
id int primary key,<br>
age int not null,<br>
name varchar(16) not null<br>
);</p>
</blockquote>
<p>向表中插入多条数据：<br>
<img src="https://img-blog.csdnimg.cn/direct/abd2de36b97640348a4091e34736bd22.png" alt="在这里插入图片描述"><br>
注意：插入的数据中，id列是随机的。<br>
<img src="https://img-blog.csdnimg.cn/direct/e5c4267241ca45cca974a68453cfe183.png" alt="在这里插入图片描述"></p>
<p>但是，当查看user表的内容时，发现id列居然是排好序的。</p>
<p>问题：1.谁做的？为什么要这么做？<br>
2.谈谈page</p>
<p><strong>简单理解的话，page就是一个内存块。<br>
但是不能简单理解。</strong></p>
<p>mysql内部一定需要且存在大量page，这就意味着要将多个page进行管理起来，那就是<strong>先描述，再组织。</strong></p>
<p>所以，不能简单地认为page是一个内存块，它是有对应的描述字段的：</p>
<pre><code class="prism language-cpp"><span class="token keyword">struct</span> <span class="token class-name">page</span>
<span class="token punctuation">{</span>
	<span class="token keyword">struct</span> <span class="token class-name">page</span><span class="token operator">*</span> prev<span class="token punctuation">;</span>
	<span class="token keyword">struct</span> <span class="token class-name">page</span><span class="token operator">*</span> next<span class="token punctuation">;</span>
	<span class="token keyword">char</span> buffer<span class="token punctuation">[</span>NUM<span class="token punctuation">]</span><span class="token punctuation">;</span>
	
<span class="token punctuation">}</span><span class="token punctuation">;</span> <span class="token operator">--</span><span class="token operator">-</span> <span class="token number">16</span>KB大小，也就是<span class="token keyword">new</span> <span class="token function">page</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
随后将所有的page进行类似“链表”形式的管理。
</code></pre>
<p>为什么MySQL和操作系统，磁盘交互，要page？</p>
<ul>
<li>1.在不需要page的情况下：</li>
</ul>
<p>假设要查找id=2的数据，这些数据是放在磁盘中保存的。<br>
MySQL就要先将id=1的数据加载进内存，再将id=2的数据加载进内存，才能查找到id=2的数据，此时就需要两次IO。<br>
如果要查找id=5的数据，就要5次IO了。</p>
<ul>
<li>2.在有page的情况下：<br>
假设要查找id=2的数据，MySQL首先从磁盘中将page页加载到内存中，而要查找的数据，就在page页中放着，所以加载page到内存后就可以直接进行查找了，此时只需要将page放到内存这1次IO.<br>
但是如果要查找id=3，id=4，id=5这些数据，就完全不需要和磁盘进行IO了，因为page页已经被加载到内存了，直接在内存中对page进行查找即可。</li>
</ul>
<p>这就很明显提高了效率。</p>
<p>问题：我怎么保证我要查找的数据就在这个page页中？<br>
答案是无法保证的，但是有很大概率在page页中，因为<strong>局部性原理</strong>。</p>
<p><strong>IO效率低下的原因往往是因为IO次数过多，而不是因为IO一次的数据量过大。</strong></p>
<p>就像是我要1次把1G的数据放到磁盘，和分100次把1G的数据放到磁盘一样。<br>
一次把1G的数据放到磁盘，只需要一次物理寻址，再将内容写入。<br>
而分100次把1G的内容放到磁盘的话，就需要100次的物理寻址了，效率自然就低。</p>
<h2><a id="3pagepage_126"></a>3.单page和多page同样存在效率低下的问题</h2>
<h3><a id="page_128"></a>单page的缺陷</h3>
<p>上面提到的page，对数据的存储如下图：</p>
<p><img src="https://img-blog.csdnimg.cn/direct/3bed2dc8bcd9450398ac678dffbdbf52.png" alt="在这里插入图片描述"><br>
由于主键的存在，MySQL会根据主键的数据，内部自己进行排序。这也回答了上面的第一个问题：这个排序的过程其实是MySQL自己干的。</p>
<p>那为什么要排序呢？<br>
<strong>本质上还是为了提高查询的效率</strong></p>
<p>因为在内部插入数据时，对数据的管理也是链表形式，而链表就是增删快，查询和修改慢，所以进行排序，是有必要的。<br>
如果运气好，就能提前遍历结束，找到数据。</p>
<hr>
<p>但是！！！</p>
<p>如果运气不好， 就需要遍历整个page表，直到查询到最后一个数据是想要的为止。<br>
<strong>这就是单page下的效率缺陷。</strong></p>
<h3><a id="page_149"></a>多page的缺陷</h3>
<p><img src="https://img-blog.csdnimg.cn/direct/91c107ef0bbe4844892414aaa247e89b.png" alt="在这里插入图片描述"><br>
知道page这个页的结构之后，那么多个页的保存，就如上图，像链表一样，一个个地连起来。</p>
<p>假如要从1000w条数据中查找，那就不止要导入一个page页到内存了，需要导入多个page页，这些page页之间，有指针互相记录地址，形成一个双链表的结果。</p>
<p>而在1000w中查询一条数据，也同样是从第一个page页中，一条条记录地往下查找，这也是一个线性的过程。</p>
<p><strong>这样找，效率也同样是太慢了！！！</strong></p>
<h2><a id="_160"></a>页目录</h2>
<h3><a id="page_163"></a>单page</h3>
<p>我们在看《谭浩强C程序设计》这本书的时候，如果我们要看&lt;指针章节&gt;，找到该章节有两种做法：</p>
<ul>
<li>从头逐页的向后翻，直到找到目标内容。<br>
通过书提供的目录，发现指针章节在234页(假设)，那么我们便直接翻到234页。同时，查找目录的方案，可以顺序找，不过因为目录肯定少，所以可以快速提高定位。</li>
<li>本质上，书中的目录，是多花了纸张的，但是却提高了效率。<br>
<strong>所以，目录，是一种“空间换时间的做法”。</strong></li>
</ul>
<p><img src="https://img-blog.csdnimg.cn/direct/4256704f9bf84b9ca5dff747cdddf867.png" alt="在这里插入图片描述"></p>
<h3><a id="page_176"></a>多page</h3>
<p>上面的单page，是在页内增加一个目录，来指向页内中的某些数据。</p>
<p>而多page情况下，在页内也同样增加目录，来对页进行管理。<br>
只是单page目录管理的是页内的行，而多page目录管理的是页。</p>
<p><strong>而页目录这种保存方法，其实是保存页page的主键大小，根据主键大小决定在B+树放的位置。</strong><br>
<img src="https://img-blog.csdnimg.cn/direct/c127598e02f24cfea9d77d207f7c8c00.png" alt="在这里插入图片描述"></p>
<p>目录的指针指向一个页中的最小的数据，另一个指针指向一个特定数据，就构成了一个范围。</p>
<p>比如一级页中，页目录指针指向的是1，和11。<br>
那这个一级目录就把页表分成了：1-&gt;10， 11-&gt;**的两个层级。<br>
这样层层管理，当来一个数据是5的时候，就会去1-&gt;10这个页范围查找，从而排除了11-&gt;*这个范围。</p>
<p>这样就不用再进行线性遍历，从而极大提高了效率。</p>
<p>而上面这个结构，不就是一棵典型的B+树吗？</p>
<h1><a id="B_198"></a>B+树为什么行！</h1>
<h3><a id="_199"></a>详谈细节</h3>
<p>需要注意的点是：</p>
<ul>
<li>1.叶子节点中保存有数据，而路上节点没有，非叶子姐点，不需要数据，所以多出来的空间，就能存储更多的目录项，那就能管理更多的page，所以这棵树就是典型的<strong>矮胖树</strong>！！！</li>
<li>2.路上途径的每个非叶子节点，都有页目录，找到目标page的IO次数就减少，就能极大地提高查找效率！！</li>
<li>3.叶子节点的页page，一定是用链表相互连接起来的。</li>
</ul>
<p>假如一个page大小16KB，全部用来存储页目录，一个指针大小4字节，那么一个page就能管理4096个page了！！！这样惊人的查找效率，谁不爱呢？</p>
<p>上面的结构，就是mysql innode db下的索引结构，一般在建表插入数据的时候，就是在该结构下进行CURD（增删查改）的！！！</p>
<p>如果我创建的表没有主键呢？<br>
如果创建的表没有主键，mysql就会生成一个隐藏的主键，放进B+树结构中，一般这个生成的主键是自增长类型的主键。</p>
<p>对于叶子节点用链表连接起来这个特性，不是mysql这个数据库实现的页page管理结构，而是B+树本身就这样的！！是mysql因为需求，选择了B+树！！</p>
<h1><a id="_218"></a>其他数据结构为什么不行?</h1>
<p>InnoDB 在建立索引结构来管理数据的时候，其他数据结构为何不行？</p>
<ul>
<li>链表？线性遍历问题，最坏情况就是遍历整个链表。</li>
<li>二叉搜索树？退化问题，可能退化成为线性结构，最坏情况也就是链表结构了。</li>
<li>AVL &amp;&amp;红黑树？虽然是平衡或者近似平衡，但是毕竟是二叉结构，相比较多阶B+，意味着树整体过高，大家都是自顶向下找，层高越低，意味着系统与硬盘更少的IO Page交互。虽然你很秀，但是有更秀的。也就是说，AVL树 &amp;&amp; 红黑树，相较于B+树来说，一个是瘦高，一个是矮胖，但是越矮，意味着IO的次数越少，效率越高。</li>
<li>Hash？官方的索引实现方式中， MySQL 是支持HASH的，不过 InnoDB 和 MyISAM 并不支持.Hash跟进其算法特征，决定了虽然有时候也很快(O(1))，不过在面对范围查找的时候就不行。<br>
这里具体的情况不是很了解，所以查了GPT：<img src="https://img-blog.csdnimg.cn/direct/871150aad3204ad283f579c56c93a42c.png" alt="在这里插入图片描述"></li>
</ul>
<p>对于B树来说，B树和B+树的区别如下：<br>
<img src="https://img-blog.csdnimg.cn/direct/8091eca8d72c457f85e6f03253c50b09.png" alt="在这里插入图片描述"></p>
<ul>
<li>一、B树的每一个节点，都会存储一定量的主键，包括路上节点。这也就意味着，有更少的空间用于记录其他的页page，就导致整体的B树结构会更高更瘦，效率问题就出现了。</li>
<li>二、B树的所有叶子节点中，没有使用链表连接起来，所以对于范围查找，就不方便，效率就低了。</li>
</ul>
<p>而B+树，完美解决了上面的两种B树的缺陷。<br>
还是那句话，虽然你很秀，但还有更秀的。</p>
<h3><a id="_238"></a>聚簇索引和非聚簇索引</h3>
<p>首先创建一个新的数据库index_db，在该库中创建一个表：test1<br>
<img src="https://img-blog.csdnimg.cn/direct/64239a2ad8be44bd99f68313fba938ac.png" alt="在这里插入图片描述"><br>
查看 默认存放数据库的目录：/var/lib/mysql/index_db目录时，发现有几个文件。</p>
<p>其中，创建一张表，就有几个文件，第一个文件是test1.frm，这个就是传说中的表结构，就存储在该文件中。第二个文件是test1.ibd，后缀名全称是：index block data，也就是索引和数据放在一起了。</p>
<p><strong>对于将索引和数据一起存放的模式，就是聚簇索引！</strong></p>
<p><img src="https://img-blog.csdnimg.cn/direct/37d45bafb2934979b72221a7fd429ac6.png" alt="在这里插入图片描述"></p>
<p>再次创建一个表：test2，默认的engine改为myisam。<br>
<img src="https://img-blog.csdnimg.cn/direct/6979492c9c0b4ddea94e2b4d3fd18df9.png" alt="在这里插入图片描述"><br>
再次查看该数据库下的文件，发现test2表是三个文件。<br>
第一个.frm文件就是表结构，第二个.myd文件，就是data数据，第三个.myi文件，就是index索引，所以，使用myisam存储引擎，就是将索引和数据分开存放。</p>
<p><strong>像这种将索引和数据分开存放的索引，就是非聚簇索引！！！</strong></p>
<p><img src="https://img-blog.csdnimg.cn/direct/5e530f32ff404277947f85ea96f0cd60.png" alt="在这里插入图片描述"></p>
<p>非聚簇索引大概长这样：<br>
B+树的叶子节点的索引存储的是数据的地址，再通过地址找到表数据，这样就实现了将索引和数据分离存储。<br>
<img src="https://img-blog.csdnimg.cn/direct/dfd935344ea347a98d30a080b42d3b4a.png" alt="在这里插入图片描述"></p>
<h2><a id="_266"></a>回表查询</h2>
<p>MySQL 除了默认会建立主键索引外，我们用户也有可能建立按照其他列信息建立的索引，一般这种索引可以叫做辅助（普通）索引。</p>
<p>对于 MyISAM ,建立辅助（普通）索引和主键索引没有差别，无非就是主键不能重复，而非主键可重复。</p>
<p>对于InnoDB 除了主键索引，用户也会建立辅助（普通）索引。<br>
<img src="https://img-blog.csdnimg.cn/direct/8a454d4b47874b6e9720b19056067b12.png" alt="在这里插入图片描述"></p>
<p>InnoDB的辅助索引如上图，除了主键索引，在用户建立的辅助索引中，也会构建一颗B+树，这棵B+树的叶子节点，不会存储数据，而是存储主键的key！！</p>
<p>所以用户的查找方式应该如下：首先遍历辅助索引，找到目标的key值，再去主键的B+树中，根据key值找到记录，这个过程就叫做回表查询！</p>
<p><img src="https://img-blog.csdnimg.cn/direct/8d37e7ca158c4194b43e2934f56927a2.png" alt="在这里插入图片描述"></p>
<p>所以建立普通索引时，直接回表查询即可，不需要再在辅助B+树中的叶子节点再存储数据。</p>
<h1><a id="_285"></a>四、索引的操作</h1>
<h2><a id="1_287"></a>1.创建主键索引</h2>
<p>法1：</p>
<blockquote>
<p>– 在创建表的时候，直接在字段名后指定 primary key<br>
create table user1(id int primary key, name varchar(30));</p>
</blockquote>
<p>法2：</p>
<blockquote>
<p>– 在创建表的最后，指定某列或某几列为主键索引<br>
create table user2(id int, name varchar(30), primary key(id));</p>
</blockquote>
<p>法3：</p>
<blockquote>
<p>create table user3(id int, name varchar(30));<br>
– 创建表以后再添加主键<br>
alter table user3 add primary key(id);</p>
</blockquote>
<p>主键索引的特点：</p>
<blockquote>
<p>一个表中，最多有一个主键索引，当然可以使符合主键 主键索引的效率高（主键不可重复） 创建主键索引的列，它的值不能为null，且不能重复<br>
主键索引的列基本上是int</p>
</blockquote>
<p>已经创建好一个含有主键索引的表后，查看表格，如下：</p>
<p><img src="https://img-blog.csdnimg.cn/direct/a9a77f30f1c44aa7b16e243b586641eb.png" alt="在这里插入图片描述"></p>
<h2><a id="2_314"></a>2.唯一索引的创建</h2>
<p>法1：</p>
<blockquote>
<p>– 在表定义时，在某列后直接指定unique唯一属性。<br>
create table user4(id int primary key, name varchar(30) unique);</p>
</blockquote>
<p>法2：</p>
<blockquote>
<p>– 创建表时，在表的后面指定某列或某几列为unique<br>
create table user5(id int primary key, name varchar(30), unique(name));</p>
</blockquote>
<p>法3：</p>
<blockquote>
<p>create table user6(id int primary key, name varchar(30)）；<br>
alter table user6 add unique(name);</p>
</blockquote>
<p>唯一索引的特点：</p>
<blockquote>
<p>一个表中，可以有多个唯一索引<br>
查询效率高<br>
如果在某一列建立唯一索引，必须保证这列不能有重复数据<br>
如果一个唯一索引上指定not null，等价于主键索引</p>
</blockquote>
<h2><a id="3_339"></a>3.普通索引的创建</h2>
<p>法1：</p>
<blockquote>
<p>create table user8(id int primary key,<br>
name varchar(20),<br>
email varchar(30),<br>
index(name) --在表的定义最后，指定某列为索引<br>
);</p>
</blockquote>
<p>法2：</p>
<blockquote>
<p>create table user9(id int primary key, name varchar(20), email<br>
varchar(30));<br>
alter table user9 add index(name); --创建完表以后指定某列为普通索引</p>
</blockquote>
<p>法3：</p>
<blockquote>
<p>create table user10(id int primary key, name varchar(20), email<br>
varchar(30));<br>
– 创建一个索引名为 idx_name 的索引<br>
create index idx_name on user10(name);</p>
</blockquote>
<p>普通索引的特点：</p>
<blockquote>
<p>一个表中可以有多个普通索引，普通索引在实际开发中用的比较多<br>
如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引</p>
</blockquote>
</div>
</body>

</html>
